import random

import numpy as np
import sqlalchemy
from fastapi import Request
from matplotlib import pyplot as plt
from pydantic import BaseModel, Field

from .api_model.response_model import ApiResponse
from .user import check_user
from .utils.path_tools import generate_img_path
from .utils.get_config import config_data
STATIC_URL = config_data['static_path']


async def get_my_grade_list(uid: str, engine):
    conn = engine.connect()
    try:
        response = ApiResponse(code=200,
                               data={
                               },
                               msg="查询成功")

        stu_grade_result = conn.execute(sqlalchemy.text("""
                                SELECT stu_detail.uid, stu_detail.stu_num, user_info.username,
                                 stu_grade.grade, stu_grade.semester,
                                 lesson_info.lesson_num, lesson_info.lesson_name
                                FROM stu_grade, stu_detail, user_info, lesson_info
                                WHERE stu_detail.uid = :uid and stu_grade.uid = :uid and user_info.uid = :uid
                                and lesson_info.lesson_num = stu_grade.lesson_num
                            """), {
            "uid": uid
        })
        conn.commit()
        stu_grade_list = stu_grade_result.fetchall()
        # print(stu_grade_result)
        if stu_grade_list:
            response.data = {
                "stu_grade_list": [
                    {
                        "grade": grade[3],
                        "semester": grade[4],
                        "lesson_num": grade[5],
                        "lesson_name": grade[6],
                    } for grade in stu_grade_list
                ]
            }
        return response

    except Exception as e:
        print(e)
        return ApiResponse(code=500, msg="查询成绩列表信息发生错误")
    finally:
        conn.close()


from .teacher import check_user_is_teacher
async def get_my_grade_list_api(request: Request, uid: str, engine):
    # print(request.state.user)
    token_uid = request.state.user.get("uid")
    teacher = check_user_is_teacher(token_uid, engine)
    if not token_uid or (token_uid != uid and not teacher):
        ApiResponse(code=400, msg=f'{token_uid} 无权限查看 {uid} ')
        print(token_uid)
    response = await get_my_grade_list(uid, engine)
    return response


async def get_class_grade_list(class_name: str, engine):
    conn = engine.connect()
    try:
        response = ApiResponse(code=200,
                               data={
                               },
                               msg="查询成功")

        # First get all students in the same major as those in the class
        class_grade_result = conn.execute(sqlalchemy.text("""
            SELECT stu_detail.uid, stu_detail.stu_num, user_info.username,
                   ROUND(stu_detail.avg_grade, 3) as avg_grade,
                   stu_detail.major
            FROM stu_detail
            JOIN user_info ON stu_detail.uid = user_info.uid
            WHERE stu_detail.class_name = :class_name
            GROUP BY user_info.uid
                                # SELECT stu_detail.uid, stu_detail.stu_num, user_info.username,
                                #  ROUND(AVG(stu_grade.grade), 3)
                                # FROM stu_grade
                                # JOIN stu_detail ON stu_grade.uid = stu_detail.uid
                                # JOIN user_info ON stu_grade.uid = user_info.uid
                                # JOIN lesson_info ON lesson_info.lesson_num = stu_grade.lesson_num
                                # WHERE stu_detail.class_name = :class_name and stu_grade.grade IS NOT NULL
                                # GROUP BY user_info.
        """), {"class_name": class_name})

        class_grade_list = class_grade_result.fetchall()

        if not class_grade_list:
            return ApiResponse(code=404, msg="班级中没有学生")

        # Get the major (assuming all students in class are from same major)
        major = class_grade_list[0][4]

        # Get all students in the same major for ranking
        major_students_result = conn.execute(sqlalchemy.text("""
            SELECT uid, ROUND(avg_grade, 3) as avg_grade
            FROM stu_detail
            WHERE major = :major AND avg_grade IS NOT NULL
            ORDER BY avg_grade DESC
        """), {"major": major})

        major_students = major_students_result.fetchall()

        # Create a dictionary for ranking lookup
        ranking_dict = {}
        for rank, (uid, grade) in enumerate(major_students, 1):
            ranking_dict[uid] = rank

        # 分组统计
        bins = [0, 60, 65, 70, 75, 80, 85, 90, 95, 100]
        hist_counts = [0] * (len(bins) - 1)
        grades = []

        processed_class_grade_list = []
        for uid, stu_num, username, grade, _ in class_grade_list:
            # Add major ranking
            major_rank = ranking_dict.get(uid, "N/A")
            processed_class_grade_list.append({
                "uid": uid,
                "stu_num": stu_num,
                "username": username,
                "avg_grade": grade,
                "major_rank": major_rank
            })

            # Collect grades for statistics
            if grade is not None:
                grades.append(grade)
                for i in range(len(bins) - 1):
                    if bins[i] <= grade < bins[i + 1]:
                        hist_counts[i] += 1
                        break

        if not grades:
            return ApiResponse(code=404, msg="没有有效的成绩数据")

        # Sort the class grade list by major rank
        processed_class_grade_list.sort(key=lambda x: x["major_rank"] if x["major_rank"] != "N/A" else float('inf'))

        average_grade = np.mean(grades)
        median_grade = np.median(grades)

        plt.figure(figsize=(12, 8))

        # 创建柱状图
        plt.bar(range(len(hist_counts)), hist_counts, width=1, align='center', edgecolor='black')
        plt.tick_params(axis='x', labelsize=24)
        plt.tick_params(axis='y', labelsize=24)
        plt.xticks(range(len(bins) - 1), [f"{bins[i]}-{bins[i + 1]}" for i in range(len(bins) - 1)])
        plt.xlabel('成绩区间', fontsize=24)
        plt.ylabel('学生人数', fontsize=24)
        plt.title('班级成绩分布图', fontsize=24)
        plt.gca().yaxis.set_major_locator(plt.MaxNLocator(integer=True))
        plt.grid(False)
        plt.axvline(x=np.digitize(average_grade, bins) - 1, color='r', linestyle='--', linewidth=4,
                    label=f'平均值: {average_grade:.2f}')
        plt.axvline(x=np.digitize(median_grade, bins) - 1, color='g', linestyle='-.', linewidth=4,
                    label=f'中位数: {median_grade:.2f}')
        plt.legend(fontsize=24)

        img_path = generate_img_path()
        plt.savefig(img_path)

        response.data = {
            "img_path": STATIC_URL + img_path[2:],
            "class_grade_list": processed_class_grade_list
        }

        return response

    except Exception as e:
        print(e)
        return ApiResponse(code=500, msg="查询成绩列表信息发生错误")
    finally:
        conn.close()



from .teacher import check_user_is_teacher
async def get_class_grade_list_api(request: Request, class_name: str, engine):
    # print(request.state.user)
    token_uid = request.state.user.get("uid")
    teacher = check_user_is_teacher(token_uid, engine)
    if not token_uid or not teacher:
        ApiResponse(code=400, msg=f'{token_uid} 无权限查看 {class_name} ')
        print(token_uid)
    response = await get_class_grade_list(class_name, engine)
    return response


async def get_lesson_grade_list(class_name: str, engine):
    conn = engine.connect()
    try:
        response = ApiResponse(code=200,
                               data={
                               },
                               msg="查询成功")

        class_grade_result = conn.execute(sqlalchemy.text("""
                                SELECT stu_detail.uid, stu_detail.stu_num, user_info.username,
                                  lesson_info.lesson_num, lesson_info.lesson_name, stu_grade.grade
                                FROM stu_grade
                                JOIN stu_detail ON stu_grade.uid = stu_detail.uid
                                JOIN user_info ON stu_grade.uid = user_info.uid
                                JOIN lesson_info ON lesson_info.lesson_num = stu_grade.lesson_num
                                WHERE stu_detail.class_name = :class_name and stu_grade.grade IS NOT NULL
                            """), {
            "class_name": class_name
        })
        conn.commit()
        class_grade_detail_list = class_grade_result.fetchall()
        # print(class_grade_detail_list)

        from collections import defaultdict
        lesson_grades = defaultdict(list)
        student_grade_details = defaultdict(list)
        lesson_info = {}
        for record in class_grade_detail_list:
            uid = record[0]
            stu_num = record[1]
            username = record[2]
            lesson_num = record[3]
            lesson_name = record[4]
            grade = float(record[5])
            lesson_grades[lesson_name].append(grade)
            student_grade_details[lesson_name].append({
                "uid": uid,
                "stu_num": stu_num,
                "username": username,
                "grade": grade
            })
            if lesson_name not in lesson_info:
                lesson_info[lesson_name] = lesson_num

        # 准备绘图数据
        lesson_names = list(lesson_grades.keys())
        grade_data = list(lesson_grades.values())

        plt.figure(figsize=(12, 10))

        box = plt.boxplot(grade_data, patch_artist=True, labels=lesson_names)

        # 添加标题和标签
        plt.title(f'{class_name} 课程成绩箱图', fontsize=24)
        plt.xlabel('课程', fontsize=24)
        plt.ylabel('成绩', fontsize=24)
        plt.xticks(rotation=45, ha='right', fontsize=24)
        plt.yticks(fontsize=24)

        # 显示网格
        plt.grid(True, linestyle='--', alpha=0.7)

        plt.tight_layout()

        img_path = generate_img_path()
        plt.savefig(img_path)

        if lesson_grades:
            response.data = {
                "img_path": STATIC_URL + img_path[2:],
                "lesson_grade_list": [
                    {
                        "lesson_num": lesson_info[lesson_name],
                        "lesson_name": lesson_name,
                        "grade_list": grades_list,
                    } for lesson_name, grades_list in student_grade_details.items()
                ]
            }

        return response

    except Exception as e:
        print(e)
        return ApiResponse(code=500, msg="查询课程成绩列表信息发生错误")
    finally:
        conn.close()


from .teacher import check_user_is_teacher
async def get_lesson_grade_list_api(request: Request, class_name: str, engine):
    # print(request.state.user)
    token_uid = request.state.user.get("uid")
    teacher = check_user_is_teacher(token_uid, engine)
    if not token_uid or not teacher:
        ApiResponse(code=400, msg=f'{token_uid} 无权限查看 {class_name} ')
        print(token_uid)
    response = await get_lesson_grade_list(class_name, engine)
    return response


async def get_semesters_api(engine):
    conn = engine.connect()
    try:
        semester_result = conn.execute(sqlalchemy.text("""
                                    SELECT semester_name from semester
                                """), {

        })
        conn.commit()
        semester_list = semester_result.fetchall()
        response = ApiResponse(code=200,
                               data={
                                   "num": random.randint(1, 100000),
                                   "semester_list": [i[0] for i in semester_list]
                               },
                               msg="")
        return response

    except Exception as e:
        print(e)
        return ApiResponse(code=500, msg="错误")
    finally:
        conn.close()
